package com.rc.saas.tenant.interceptor;

import com.rc.saas.tenant.common.constant.ShiroConstants;
import com.rc.saas.tenant.model.tenant.TenantUser;
import com.rc.saas.tenant.base.BaseSearch;
import net.sf.jsqlparser.JSQLParserException;
import net.sf.jsqlparser.expression.Expression;
import net.sf.jsqlparser.expression.StringValue;
import net.sf.jsqlparser.expression.operators.conditional.AndExpression;
import net.sf.jsqlparser.expression.operators.conditional.OrExpression;
import net.sf.jsqlparser.expression.operators.relational.EqualsTo;
import net.sf.jsqlparser.parser.CCJSqlParserUtil;
import net.sf.jsqlparser.schema.Column;
import net.sf.jsqlparser.schema.Table;
import net.sf.jsqlparser.statement.Statement;
import net.sf.jsqlparser.statement.select.PlainSelect;
import net.sf.jsqlparser.statement.select.Select;
import net.sf.jsqlparser.statement.select.SubSelect;
import net.sf.jsqlparser.util.TablesNamesFinder;
import org.apache.commons.lang3.StringUtils;
import org.apache.ibatis.executor.Executor;
import org.apache.ibatis.executor.statement.RoutingStatementHandler;
import org.apache.ibatis.executor.statement.StatementHandler;
import org.apache.ibatis.mapping.BoundSql;
import org.apache.ibatis.mapping.MappedStatement;
import org.apache.ibatis.mapping.SqlCommandType;
import org.apache.ibatis.plugin.*;
import org.apache.ibatis.reflection.MetaObject;
import org.apache.ibatis.reflection.SystemMetaObject;
import org.apache.ibatis.session.ResultHandler;
import org.apache.ibatis.session.RowBounds;
import org.apache.logging.log4j.LogManager;
import org.apache.logging.log4j.Logger;
import org.apache.shiro.SecurityUtils;
import org.apache.shiro.session.Session;

import java.sql.Connection;
import java.util.*;

/**
 * 原生SQL和MYBATIS查询拦截器
 * query:会在所有select查询方法调用时被执行(这个是最常用的被拦截的方法),原生SQL
 * prepare:拦截StatementHandler类的prepare方法，其参数有两个，分别是Connection.class,Integer.class。也就是在sql执行预编译前
 * Created by sven on 2019/4/30
 */
//@Intercepts({
//        @Signature(type = Executor.class,
//                method = "query",
//                args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class})})
//@Intercepts({
//        @Signature(method = "query", type = StatementHandler.class, args = {java.sql.Statement.class, ResultHandler.class})})
@Intercepts({
        @Signature(method = "query", type = Executor.class, args = {MappedStatement.class, Object.class, RowBounds.class, ResultHandler.class}),
        @Signature(method = "prepare", type = StatementHandler.class, args = {Connection.class, Integer.class})})
public class TenantQueryInterceptor implements Interceptor {
    private static Logger logger = LogManager.getLogger(TenantQueryInterceptor.class);

    private String tenantCodeColumn = "tenant_code";
    private String tenantOperatorCodeColumn = "tenant_operator_code";

    private TenantUser user = null;

    @Override
    public Object intercept(Invocation invocation) throws Throwable {
        // 3. 获取当前用户信息
        Session session = SecurityUtils.getSubject().getSession();
        TenantUser user = (TenantUser) session.getAttribute(ShiroConstants.SESSION_TENANT_USER_KEY);

        if (invocation.getTarget() instanceof MappedStatement) {
            interceptQuery(invocation, user);
        } else if (invocation.getTarget() instanceof StatementHandler) {
            interceptPrepare(invocation, user);
        } else if (invocation.getTarget() instanceof RoutingStatementHandler) {
            RoutingStatementHandler statementHandler = (RoutingStatementHandler) invocation.getTarget();
            StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(statementHandler, "delegate");
            BoundSql boundSql = delegate.getBoundSql();
            //Object obj = boundSql.getParameterObject();

            logger.warn("RoutingStatementHandler 查询拦截器:{}", boundSql.getSql());

            //if (obj instanceof Page<?>) {
            //    Page<?> page = (Page<?>) obj;
            //    //通过反射获取delegate父类BaseStatementHandler的mappedStatement属性
            //    MappedStatement mappedStatement = (MappedStatement)ReflectHelper.getFieldValue(delegate, "mappedStatement");
            //    //拦截到的prepare方法参数是一个Connection对象
            //    Connection connection = (Connection)invocation.getArgs()[0];
            //    //获取当前要执行的Sql语句，也就是我们直接在Mapper映射语句中写的Sql语句
            //    String sql = boundSql.getSql();
            //    //给当前的page参数对象设置总记录数
            //    this.setTotalRecord(page,
            //            mappedStatement, connection);
            //    //获取分页Sql语句
            //    String pageSql = this.getPageSql(page, sql);
            //    //利用反射设置当前BoundSql对应的sql属性为我们建立好的分页Sql语句
            //    ReflectHelper.setFieldValue(boundSql, "sql", pageSql);
            //}
        }

        //继续执行
        return invocation.proceed();
    }

    @Override
    public Object plugin(Object target) {
        //做拦截处理:默认都是这一句
        return Plugin.wrap(target, this);
        //不做拦截: return target;
    }

    @Override
    public void setProperties(Properties properties) {
        //tenantIdColumn = properties.getProperty("tenantIdColumn");
        //logger.warn("属性加载 tenantIdColumn:{}", tenantIdColumn);
    }

    /**
     * MYBATIS查询
     * @param invocation
     * @return
     */
    public void interceptPrepare(Invocation invocation, TenantUser user) {
        //1 获取方法名，如 com.rc.saas.tenant.mapper.tenant.TenantUserMapper.selectByExample
        StatementHandler statementHandler = (StatementHandler) invocation.getTarget();
        //由于mappedStatement为protected的，所以要通过反射获取
        MetaObject metaStatementHandler = SystemMetaObject.forObject(statementHandler);
        //mappedStatement中有我们需要的方法id
        MappedStatement mappedStatement = (MappedStatement) metaStatementHandler.getValue("delegate.mappedStatement");

        logger.warn("interceptPrepare查询拦截器, mapperid:{}", mappedStatement.getId());

        SqlCommandType commandType = mappedStatement.getSqlCommandType();
        if (commandType.equals(SqlCommandType.SELECT)) {
            //2 获取原生SQL语句
            //BoundSql boundSql = (BoundSql) mappedStatement.getBoundSql(mappedStatement);
            StatementHandler delegate = (StatementHandler) ReflectUtil.getFieldValue(statementHandler, "delegate");
            BoundSql boundSql = delegate.getBoundSql();

            //logger.warn("interceptPrepare 查询拦截器, SQL:{}", boundSql.getSql());

            //拿到当前绑定Sql的参数对象，就是我们在调用对应的Mapper映射语句时所传入的参数对象
            Object parameterObject = boundSql.getParameterObject();
            if (parameterObject instanceof BaseSearch) {
                BaseSearch search = (BaseSearch) parameterObject;
                if (StringUtils.isNotBlank(search.getTenantCode()) && !search.getTenantCode().equals(user.getTenantCode())) {
                    logger.error("interceptPrepare查询失败,当前数据的运营商ID跟登录户ID不一致, 当前数据的运营商ID:{},登录户ID:{}", search.getTenantCode(), user.getTenantCode());
                    throw new RuntimeException("interceptPrepare查询失败,当前数据的运营商ID跟登录户ID不一致");
                }
            }

            //if (parameterObject instanceof Map) {
            //    Map<String, Object> paramMap = (Map<String, Object>) parameterObject;
            //    Iterator<String> keys = paramMap.keySet().iterator();
            //    while (keys.hasNext()) {
            //        String key = keys.next();
            //        Object obj = paramMap.get(key);
            //        logger.warn("拿到当前绑定Sql的参数对象, :{}=:{}", key,obj);
            //    }
            //}
            //MetaObject mo = (MetaObject) ReflectUtil.getFieldValue(boundSql, "metaParameters");

            //List<ParameterMapping> parameterMappings = boundSql.getParameterMappings();
            //if (parameterMappings == null || parameterMappings.isEmpty()) {
            //    //TODO 没有参数，检查常量
            //}
            //for (ParameterMapping pm : boundSql.getParameterMappings()) {
            //    String propertyName = pm.getProperty();
            //}
        }
    }

    /**
     * 原生SQL
     * @param invocation
     */
    public void interceptQuery(Invocation invocation, TenantUser user) {
        //Object[] args = invocation.getArgs();
        //MappedStatement ms = (MappedStatement) args[0];
        //Object parameterObject1 = args[1];
        //RowBounds rowBounds = (RowBounds) args[2];
        //ResultHandler resultHandler = (ResultHandler) args[3];

        // 根据签名指定的args顺序获取具体的实现类
        // 1. 获取MappedStatement实例, 并获取当前SQL命令类型
        MappedStatement statement = (MappedStatement) invocation.getArgs()[0];
        SqlCommandType commandType = statement.getSqlCommandType();

        // 2. 获取当前正在被操作的类, 有可能是Java Bean, 也可能是普通的操作对象, 比如普通的参数传递
        // 普通参数, 即是 @Param 包装或者原始 Map 对象, 普通参数会被 Mybatis 包装成 Map 对象
        // 即是 org.apache.ibatis.binding.MapperMethod$ParamMap
        Object parameterObject = invocation.getArgs()[1];
        // 获取拦截器指定的方法类型, 通常需要拦截 update
        String methodName = invocation.getMethod().getName();

        String mapperId = statement.getId();
        logger.warn("interceptQuery查询拦截器, mapperid:{},methodName:{}, commandType:{}", mapperId, methodName, commandType);

        // 3. 获取当前用户信息
        try {
            Session session = SecurityUtils.getSubject().getSession();
            user = (TenantUser) session.getAttribute(ShiroConstants.SESSION_TENANT_USER_KEY);
        } catch (Exception e) {
            logger.error(e.getMessage());
        }

        if (user != null) {
            //TODO 如果是selectByExample方法，WHERE增加TENANT_CODE条件

            if (methodName.equals("query")) {
                if (commandType.equals(SqlCommandType.SELECT)) {
                    // 拦截sql
                    //Object[] args = invocation.getArgs();
                    //MappedStatement statement = (MappedStatement) args[0];
                    //Object parameterObject = args[1];
                    BoundSql boundSql = statement.getBoundSql(parameterObject);

                    //StatementHandler handler = (StatementHandler) invocation.getTarget();
                    //由于mappedStatement为protected的，所以要通过反射获取
                    //MetaObject statementHandler = SystemMetaObject.forObject(handler);
                    //mappedStatement中有我们需要的方法id
                    //MappedStatement mappedStatement = (MappedStatement) statementHandler.getValue("delegate.mappedStatement");

                    String oldSQL = boundSql.getSql();
                    logger.warn("=========== interceptQuery获取到的SQL ===========");
                    logger.warn(oldSQL);

                    //是否拦截的Mapper方法
                    if (mapperMethodFilter(mapperId)) {
                        //jsqlparse where 解析器改写
                        //String newSQL = addTenantCodeInWhere(oldSQL);
                        //logger.warn("=========== 更改后到的SQL ===========" + "\r\n" + newSQL);

                        //if (newSQL != null) {
                        //    ReflectUtil.setFieldValue(boundSql, "sql", newSQL);
                        //}
                    }

                    //statementHandler.setValue("delegate.boundSql.sql", newSql);

                    //更新SQL
                    //ReflectUtil.setFieldValue(boundSql, "sql", newSQL);
                }
            }

            //throw new RuntimeException("Prohibit the use of SQL statements without where conditions.originSql"+originSql);
        } else {
            logger.warn("尚未登录...");
        }
    }

    /**
     * 添加添加tenantCode条件
     *
     * @param oldSQL
     * @return
     * @throws JSQLParserException
     */
    //private boolean searchTenantCodeInWhere(String oldSQL) throws JSQLParserException {
    //    Statement stmt = CCJSqlParserUtil.parse(oldSQL);
    //
    //    if (stmt instanceof Select) {
    //        Select select = (Select) stmt;
    //        PlainSelect ps = (PlainSelect) select.getSelectBody();
    //        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
    //
    //        //如果已有TENANT_CODE就不添加
    //        Expression getwhere = ps.getWhere();
    //        if (getwhere != null) {
    //            String wherestr = getwhere.toString().toLowerCase();
    //            if (wherestr.indexOf(tenantCodeColumn) >= 0) {
    //                logger.warn("原SQL已有TENANT_CODE:{}", oldSQL);
    //                return select.toString();
    //            }
    //        }
    //
    //
    //        return select.toString();
    //    }
    //
    //    return null;
    //}

    /**
     * 添加添加tenantCode条件
     *
     * @param oldSQL
     * @return
     * @throws JSQLParserException
     */
    private String addTenantCodeInWhere(String oldSQL) throws JSQLParserException {
        Statement stmt = CCJSqlParserUtil.parse(oldSQL);

        if (stmt instanceof Select) {
            Select select = (Select) stmt;
            PlainSelect ps = (PlainSelect) select.getSelectBody();
            TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();

            //如果已有TENANT_CODE就不添加
            Expression getwhere = ps.getWhere();
            if (getwhere != null) {
                String wherestr = getwhere.toString().toLowerCase();
                if (wherestr.indexOf("tenant_code =") >= 0) {
                    logger.warn("原SQL已有TENANT_CODE:{}", oldSQL);
                    return select.toString();
                }
            }

            //表信息
            List<String> tableList = tablesNamesFinder.getTableList(select);
            if (tableList.size() == 0) {
                return select.toString();
            }

            //给查询from table 添加条件
            for (String table : tableList) {
                //表是否有tenant_code字段
                if (tableNameFilter(table)) {

                    if (ps.getWhere() != null) {
                        // 处理主表的where条件
                        AndExpression where = addAndExpression(stmt, table, ps.getWhere());
                        // form 和 join 中加载的表
                        if (where != null) {
                            ps.setWhere(where);
                        } else {
                            //子查询中的表
                            findSubSelect(stmt, ps.getWhere());
                        }
                    } else {
                        ps.setWhere(addEqualsTo(stmt, table));
                    }
                }
            }

            return select.toString();
        }

        return null;
    }

    /**
     * 多条件情况下，使用AndExpression给where条件加上tenantid条件
     *
     * @param table
     * @param where
     * @return
     * @throws Exception
     */
    public AndExpression addAndExpression(Statement stmt, String table, Expression where) {
        EqualsTo equalsTo = addEqualsTo(stmt, table);
        if (equalsTo != null) {
            return new AndExpression(equalsTo, where);
        } else {
            return null;
        }
    }

    /**
     * 创建一个 EqualsTo相同判断 条件
     *
     * @param stmt  查询对象
     * @param table 表名
     * @return “A=B” 单个where条件表达式
     * @throws Exception
     */
    public EqualsTo addEqualsTo(Statement stmt, String table) {
        EqualsTo equalsTo = new EqualsTo();
        String aliasName;
        aliasName = getTableAlias(stmt, table);
        if (aliasName != null) {
            //别名.字段
            //equalsTo.setLeftExpression(new Column(aliasName + '.' + getTenantIdColumn()));
            //表名.字段
            equalsTo.setLeftExpression(new Column(table + '.' + tenantCodeColumn));

            //运营商设置
            equalsTo.setRightExpression(new StringValue(user.getTenantCode()));
            return equalsTo;
        } else {
            return null;
        }
    }

    /**
     * 获取sql送指定表的别名你，没有别名则返回原表名 如果表名不存在返回null
     * 【仅查询from和join 不含 IN 子查询中的表 】
     *
     * @param stmt
     * @param tableName
     * @return
     */
    public String getTableAlias(Statement stmt, String tableName) {
        String alias = null;

        if (stmt instanceof Select) {
            Select select = (Select) stmt;

            PlainSelect ps = (PlainSelect) select.getSelectBody();

            // 判断主表的别名
            if (((Table) ps.getFromItem()).getName().equalsIgnoreCase(tableName)) {
                alias = ps.getFromItem().getAlias() != null ? ps.getFromItem().getAlias().getName() : tableName;
            }
        }
        return alias;
    }

    /**
     * 针对子查询中的表别名查询
     *
     * @param subSelect
     * @param tableName
     * @return
     */
    public String getTableAlias(SubSelect subSelect, String tableName) {
        PlainSelect ps = (PlainSelect) subSelect.getSelectBody();
        // 判断主表的别名
        String alias = null;
        if (((Table) ps.getFromItem()).getName().equalsIgnoreCase(tableName)) {
            if (ps.getFromItem().getAlias() != null) {
                alias = ps.getFromItem().getAlias().getName();
            } else {
                alias = tableName;
            }
        }
        return alias;
    }

    /**
     * 递归处理 子查询中的tenantid-where
     *
     * @param stmt  sql查询对象
     * @param where 当前sql的where条件 where为AndExpression或OrExpression的实例，解析其中的rightExpression，然后检查leftExpression是否为空，
     *              不为空则是AndExpression或OrExpression，再次解析其中的rightExpression
     *              注意tenantid-where是加在子查询上的
     */
    void findSubSelect(Statement stmt, Expression where) {

        // and 表达式
        if (where instanceof AndExpression) {
            AndExpression andExpression = (AndExpression) where;
            if (andExpression.getRightExpression() instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) andExpression.getRightExpression();
                doSelect(stmt, subSelect);
            }
            if (andExpression.getLeftExpression() != null) {
                findSubSelect(stmt, andExpression.getLeftExpression());
            }
        } else if (where instanceof OrExpression) {
            //  or表达式
            OrExpression orExpression = (OrExpression) where;
            if (orExpression.getRightExpression() instanceof SubSelect) {
                SubSelect subSelect = (SubSelect) orExpression.getRightExpression();
                doSelect(stmt, subSelect);
            }
            if (orExpression.getLeftExpression() != null) {
                findSubSelect(stmt, orExpression.getLeftExpression());
            }
        }
    }

    /**
     * 处理select 和 subSelect
     *
     * @param stmt   查询对象
     * @param select
     * @return
     * @throws Exception
     */
    Expression doSelect(Statement stmt, Expression select) {
        PlainSelect ps = null;
        boolean hasSubSelect = false;

        if (select instanceof SubSelect) {
            ps = (PlainSelect) ((SubSelect) select).getSelectBody();
        }
        if (select instanceof Select) {
            ps = (PlainSelect) ((Select) select).getSelectBody();
        }

        TablesNamesFinder tablesNamesFinder = new TablesNamesFinder();
        List<String> tableList = tablesNamesFinder.getTableList(select);
        if (tableList.size() == 0) {
            return select;
        }
        for (String table : tableList) {
            // sql 包含 where 条件的情况 使用 addAndExpression 连接 已有的条件和新条件
            if (ps.getWhere() == null) {
                AndExpression where = addAndExpression(stmt, table, ps.getWhere());
                // form 和 join 中加载的表
                if (where != null) {
                    ps.setWhere(where);
                } else {
                    // 如果在Statement中不存在这个表名，则存在于子查询中
                    hasSubSelect = true;
                }
            } else {
                // sql 不含 where条件 新建一个EqualsTo设置为where条件
                EqualsTo equalsTo = addEqualsTo(stmt, table);
                ps.setWhere(equalsTo);
            }
        }

        if (hasSubSelect) {
            //子查询中的表
            findSubSelect(stmt, ps.getWhere());
        }
        return select;
    }

    //public String getTenantIdColumn() {
    //    return tenantIdColumn;
    //}
    //
    //public void setTenantIdColumn(String tenantIdColumn) {
    //    this.tenantIdColumn = tenantIdColumn;
    //}

    /**
     * 是否拦截的Mapper方法
     * @param mapperId
     * @return
     */
    public boolean mapperMethodFilter(String mapperId) {
        return !(mapperMethodWhiteList.contains(mapperId) || mapperId.startsWith("com.rc.saas.mapper.system.TenantMapper"));
    }

    /**
     * 是否拦截的Mapper方法
     * @param tableName
     * @return
     */
    public boolean tableNameFilter(String tableName) {
        return !(tableNameWhiteList.contains(tableName));
    }

    /**
     * 不需拦截的table白名单
     */
    private static List<String> mapperMethodWhiteList = new ArrayList<String>() {{
        add("TenantUserMapper");
        add("TenantMenuMapper");
        //add("AccountMapper.getUserByloginName");
        //add("SysdataBaseMapper.getmaxsysdataid");
    }};

    /**
     * 不需拦截的table白名单
     */
    private static List<String> tableNameWhiteList = new ArrayList<String>() {{
        add("cms_menu");
        add("cms_organization");
        add("cms_role");
        add("cms_role_menu");
        add("cms_user");
        add("cms_user_role");
    }};
}
